﻿using MQTT.Server.DataBase;
using MQTT.Server.GlobalConfig;
using MQTT.Server.Helper;
using Newtonsoft.Json;
using SqlSugar;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace MQTT.Server.ExtensionServer
{
    /// <summary>
    /// SqlSugar 启动配置
    /// </summary>
    public static class SqlSugarExtension
    {

        /// <summary>
        /// 注册SqlSugar 服务
        /// </summary>
        /// <param name="services"></param>
        public static async void AddSqlSugarSetup(this IServiceCollection services, AppConfig appConfig)
        {
            if (services == null) throw new ArgumentNullException(nameof(services));
            //注入 ORM
            SqlSugarScope sqlSugar = new SqlSugarScope(new ConnectionConfig()
            {
                //数据库连接
                ConnectionString = appConfig.ConnectionString,
                //判断数据库类型
                DbType = appConfig.Type,
                //是否开启自动关闭数据库连接-//不设成true要手动close
                IsAutoCloseConnection = true,


                ConfigureExternalServices = new ConfigureExternalServices()
                {
                    EntityService = (x, y) =>
                    {
                        y.DbColumnName = UtilMethods.ToUnderLine(y.DbColumnName); //驼峰转下划线方法  

                        if (appConfig.Type == DbType.MySql && y.DataType == "varchar(max)")
                        {
                            y.DataType = "longtext";
                        }
                    },
                    EntityNameService = (x, y) =>
                    {
                        y.DbTableName = UtilMethods.ToUnderLine(y.DbTableName);//ToUnderLine驼峰转下划线方
                    }
                }

            }, db =>
            {
                if (appConfig.ListenDatabase)
                {
                    //SQL执行完
                    db.Aop.OnLogExecuted = (sql, pars) =>
                    {
                        //执行完了可以输出SQL执行时间 (OnLogExecutedDelegate) 
                        //Console.Write("time:" + sqlSugar.Ado.SqlExecutionTime.ToString());
                        Console.WriteLine(sql);
                        //Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
                    };
                    db.Aop.OnLogExecuting = (sql, pars) => //SQL执行前
                    {
                        //获取原生SQL推荐 5.1.4.63  性能OK
                        Console.WriteLine(sql);
                        //Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));

                        //获取无参数化SQL 影响性能只适合调试
                        //UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
                    };
                    db.Aop.OnError = (exp) =>//SQL报错
                    {
                        //获取原生SQL推荐 5.1.4.63  性能OK
                        Console.WriteLine(exp.Sql);
                        //Console.WriteLine(UtilMethods.GetNativeSql(exp.Sql, (SugarParameter[])exp.Parametres)); 

                        //获取无参数SQL对性能有影响，特别大的SQL参数多的，调试使用
                        //UtilMethods.GetSqlString(DbType.SqlServer,exp.sql,exp.parameters)           
                    };
                    db.Aop.OnExecutingChangeSql = (sql, pars) => //可以修改SQL和参数的值
                    {
                        //sql=newsql
                        //foreach(var p in pars) //修改
                        return new KeyValuePair<string, SugarParameter[]>(sql, pars);
                    };
                }

            });

            services.AddSingleton<ISqlSugarClient>(sqlSugar);

            //建库
            if (appConfig.CreateDb)
            {
                CreateDatabase(sqlSugar);
            }
            //建表
            if (appConfig.SyncStructure)
            {
                SyncStructure(sqlSugar);
            }
            if (appConfig.SyncData)
            {
                await SyncData(sqlSugar);
            }
        }


        /// <summary>
        /// 建库 如果不存在创建数据库、存在不会重复创建
        /// </summary>
        /// <param name="sqlSugar"></param>
        /// <returns></returns>
        public static void CreateDatabase(SqlSugarScope sqlSugar)
        {
            try
            {
                Console.WriteLine("开始创建数据库");
                sqlSugar.DbMaintenance.CreateDatabase();
                Console.WriteLine("\r\n创建数据库完成");
            }
            catch (Exception ex)
            {
                Console.WriteLine("\r\n创建数据库发生异常" + ex.Message);
            }
        }



        /// <summary>
        /// 同步表
        /// </summary>
        /// <param name="sqlSugar"></param>
        /// <param name="dbConfig"></param>
        public static void SyncStructure(SqlSugarScope sqlSugar, AppConfig config = null)
        {
            try
            {
                Console.WriteLine("\r\n开始创建表...");

                //增加表往数组中丢
                //https://www.donet5.com/Home/Doc?typeId=1206
                sqlSugar.CodeFirst.InitTables(new Type[]
                {
                    typeof(AppLogEntity),
                    typeof(UserEntity)
                });
                Console.WriteLine("\r\n表创建完成...");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        /// <summary>
        /// 同步数据
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public static async Task SyncData(SqlSugarScope sqlSugar)
        {
            try
            {
                Console.WriteLine("\r\n开始同步数据...");
                List<UserEntity> users = new List<UserEntity>()
                {
                    new UserEntity() {UserName = "shineyoung" , IsDeleted = false,Remark="MQTT连接验证账号",Password = MD5Encrypt.Encrypt32("shineyoung.com") },
                    new UserEntity() {UserName = "shineyoung1" , IsDeleted = false,Remark="MQTT连接验证账号",Password = MD5Encrypt.Encrypt32("shineyoung1.com") },
                };
                sqlSugar.BeginTran();
                await sqlSugar.Insertable(users).ExecuteCommandAsync();
                sqlSugar.CommitTran();
                Console.WriteLine("\r\n同步数据成功...");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}
